﻿using System;
using System.Text;
using AutoMapper;
using H2.NET.Common;
using H2.NET.Data.Common;
using H2.NET.Web.Management.Config.Service;
using H2.NET.Web.Pages.SMD.Dto;
using H2.NET.Web.Pages.SMD.Model;
using H2.NET.Web.Workflow.Helper;
using H2.NET.Web.Workflow.Models;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using H2.NET.Web.API.Model;
using H2.NET.DBUtility;
using System.Data.SqlClient;

namespace H2.NET.Web.Pages.SMD.Service
{
    public static class StoreService
    {
        public static bool IsExistSn(string sn)
        {
            return (t_smd_Store._EapproveSN == sn).Count<t_smd_Store>(Storage.Context3) > 0;
        }

        public static string Address(string address)
        {
            if (address.Split('省').Count() > 2)
            {
                return address.Substring(address.IndexOf("省") + 1, address.Length - address.IndexOf("省") - 1);
            }
            if (address.Split('市').Count() > 2)
            {
                return address.Substring(address.IndexOf("市") + 1, address.Length - address.IndexOf("市") - 1);
            }
            return address;
        }

        /// <summary>
        /// 该授权号的门店是否存在
        /// </summary>
        /// <param name="authid">门店授权号</param>
        /// <returns>true：存在 false：不存在</returns>
        public static bool IsExistAuthID(string authid)
        {
            return (t_smd_Store._AuthID == authid).Count<t_smd_Store>(Storage.Context3) > 0;
        }

        public static t_smd_Store GetSmdStoreById(int storeid)
        {
            var store = (t_smd_Store._StoreID == storeid).ListFirst<t_smd_Store>(Storage.Context3);
            return store ?? new t_smd_Store();
        }

        public static t_smd_Store GetSmdStoreByAuthID(string authid)
        {
            var store = (t_smd_Store._AuthID == authid.Trim()).ListFirst<t_smd_Store>(Storage.Context3);
            return store ?? new t_smd_Store();
        }

        public static List<t_smd_Store> FindSmdByDealerId(int dealerId)
        {
            return (t_smd_Store._DealerID == dealerId).List<t_smd_Store>(Storage.Context3).ToList();
        }

        public static List<t_smd_Store> FindSmdByStoreIds(List<int> ids)
        {
            return (t_smd_Store._StoreID.In(ids)).List<t_smd_Store>(Storage.Context3).ToList();
        }

        public static void SaveStore(t_StoreMasterData model, ref t_smd_Store store, ref t_smd_Device device ,bool isCover)
        {
            var isNewStore = false;
            var configService = new ConfigService();
            var dealer = DealerService.GetByCode(model.DealerCode);

            #region Add By Tony 20160524
            var subdealer = new t_smd_Dealer();

            //直营1；分销2
            int ownerType = 1;

            if (!string.IsNullOrEmpty(model.SubDealerCode))
            {
                subdealer = DealerService.GetByCode(model.SubDealerCode.ToString());

                ownerType = 2;
            }
            #endregion



            // 如果存在，返回门店信息，如果不存在，返回一个new store
            store = GetSmdStoreByAuthID(model.AuthID);
            isNewStore = store.StoreID == 0;

            if (store.StoreID == 0 && isCover)
            {
                throw new Exception("对不起，您选择了覆盖，但是在门店主数据库并未找到该授权号的门店信息！");
            }

            store.AuthID = model.AuthID;
            

            // 所有逻辑定时服务已经处理，所以这里直接读取数据即可
            store.StoreType = model.StoreType.Contains("Mass") ? "MASS" : model.StoreType;
            store.StoreApprovedDate = model.StoreApprovedDate;
            if (model.NewFitment != "仅调色机申请" || isNewStore)
            {
                store.EapproveSN = model.EapproveSN;
            }
            store.NewFitment = model.NewFitment;

            store.StoreStatus = store.StoreReportStatus = "CRM导入无状态"; //取消所有E-approve导入无状态，EP系统已切换为CRM
            // 除“DDCN”，“DCSS”之外，仅调色机申请改为营业状态
            var storeTypes = new List<string> { "DDCN", "DCSS" };

            //if (model.NewFitment == "仅调色机申请") 该逻辑已无需
            //{
            //    //if (!storeTypes.Contains(model.StoreType))
            //    store.StoreStatus = "营业";
            //    store.StoreReportStatus = "营业";
            //}

            if (model.NewFitment == "翻新" || model.NewFitment == "从DCSS升级")
            {
                store.StoreReportStatus = "营业";
            }


            if (!isNewStore)
            {
                Storage.Save(Storage.Context3, store);
            }

            // 选择了覆盖数据、新门店，这些数据都需要覆盖一遍
            if (isCover || isNewStore)
            {
                store.Channel = model.Channel;
                store.Region = model.AreaName;
                store.Province = model.ProvinceName;
                store.CityName = model.CityName;
                store.MallShortName = model.MallShortName;
                store.MallName = model.MallName;
                store.HouseNumber = model.HouseNumber;
                store.StoreName = model.StoreName;
                store.ContactPerson = model.ContractPerson;
                store.PhoneNumber = model.Telephone;
                store.StoreAddress = model.Address;
                store.StoreSize = model.DisplayArea == null ? 0 : model.DisplayArea.Value;
                store.DealerID = dealer.DealerID;
                store.FirstStartDate = model.OpenDate;
                store.CityClass = configService.getCityClassByCityName(store.CityName);
                //store.CityClass = dealer.CityClass; 
                store.StoreStatus = model.Status;
                store.IsColourService = model.IsColourService;
                store.IsTintingService = model.IsTintingService;
                store.ShowInWebsite = model.ShowInWebsite;
                store.ShopSeq = model.ShopSeq;
                store.CurrentStartDate = model.CurrentStartDate;
                store.LastRenewDate = model.LastRenewDate;


                #region Add By Tony 20160524
                store.AccountOwner = model.AccountOwner;
                if (subdealer != null)
                {
                    store.SubDealerCode = subdealer.DealerCode;
                    store.SubDealerName = subdealer.Name;
                }
                store.BMCCode = model.BMCCode;
                store.BMCName = model.BMCName;
                store.WechatId = model.WechatId;
                store.District = model.District;
                #endregion
            }

            Storage.Save(Storage.Context3, store);

            // 保存设备主数据
            if (StoreMasterDataService.IsExistDevice(model))
            {
                var isNewDevice = false;
                device = DeviceService.GetSmdDeviceByFullSetSN(model.DeviceFullSetSN);
                isNewDevice = device.DeviceID == 0;
                device.AuthID = model.AuthID;

                if (isNewDevice || isCover)
                {
                    device.FullSetSN = model.DeviceFullSetSN;
                    device.DispenserModel = model.DispenseModel;
                    device.ShakerModel = model.ShakerModel;
                    device.PcModel = model.PcModel;
                    device.BuyMachineDeposit = model.BuyMachineDeposit;

                    device.Status = model.DeviceStatus;
                    device.DeviceApprovedDate = model.DeviceApprovedDate;
                    device.EapproveSN = model.DeviceEapproveSN;
                    device.PurchasedDate = model.DevicePurchasedDate;
                    device.ApplicationType = model.ApplicationType;
                    #region Add By Tony 20160524
                    device.IsTransferMachine = model.IsTransferMachine;
                    device.CustomerDevice = model.CustomerDevice;
                    #endregion
                }
            }
        }

        public static string GetStoreType(string type)
        {
            if (type == null || type.Contains(')')) return type;

            var store_type = type;

            var configService = new ConfigService();

            var dt_storeType = configService.GetList("ParentId = (select configid from t_smd_Config where code='S_MDLX') order by seq").Tables[0];

            foreach (DataRow row in dt_storeType.Rows)
            {

                if (!string.IsNullOrEmpty(type) && type.StartsWith(row["code"].ToString()))
                {
                    store_type = row["name"].ToString();
                    continue;
                }
            }

            return store_type;
        }

        public static string GetStoreTypeCode(string type)
        {
            var store_type = type;

            var configService = new ConfigService();

            var dt_storeType = configService.GetList("ParentId = (select configid from t_smd_Config where code='S_MDLX') order by seq").Tables[0];

            foreach (DataRow row in dt_storeType.Rows)
            {

                if (!string.IsNullOrEmpty(type) && type.StartsWith(row["code"].ToString()))
                {
                    store_type = row["code"].ToString();
                    continue;
                }
            }
            if (store_type.Contains("多乐士"))
            {
                store_type = "DIS";
            }
            else if (store_type.Contains("来威"))
            {
                store_type = "LIS";
            }
            return store_type;
        }

        public static GridData<SmdStoreDto> SearchStoreDto(int page, int pageSize, string orderby, List<Condition> condition, string storeIds, string taskid = "")
        {
            // 条件
            string whereStr = " where 1=1";
            if (condition.Any())
            {
                var cd = condition.First();
                whereStr = " where (AuthID like '%" + cd.value + "%'";
                whereStr += " or StoreName like '%" + cd.value + "%'";
                whereStr += " or StoreAddress like '%" + cd.value + "%')";
            }

            if (!string.IsNullOrEmpty(taskid))
            {
                var taskapi = new TaskAPI(taskid);
                var region = taskapi.GetVariableBySingleValue("Region");
                if (!string.IsNullOrEmpty(region))
                {
                    whereStr += " and region='" + region.Trim() + "'";
                }
                var province = taskapi.GetVariableBySingleValue("Province");
                if (!string.IsNullOrEmpty(province))
                {
                    whereStr += " and province='" + province.Trim() + "'";
                }
                var channel = taskapi.GetVariableBySingleValue("Channel");
                if (!string.IsNullOrEmpty(channel))
                {
                    var channel_cn = channel.Trim() == "DULUX" ? "多乐士" : "来威";
                    whereStr += " and channel='" + channel_cn + "'";
                }
            }


            if (storeIds != "")
            {
                whereStr += " AND StoreID not in (" + storeIds + ")";
            }
            // 总数
            string sqlStr = "select count(1) from t_smd_store ";
            int count = Utils.ObjToInt(Storage.Context3.ExecuteScalar(new Command(sqlStr + whereStr)), 0);

            // 结果集
            var list = new List<SmdStoreDto>();
            sqlStr = "select * from t_smd_store " + whereStr;

            if (string.IsNullOrEmpty(orderby))
            {
                orderby = "storeid desc";
            }
            sqlStr = string.Format(";with temp as(\r\n" +
                "	select row_number() over(order by sub.{3}) as rownum, sub.* from ({0}) sub\r\n)\r\n" +
                "select * from temp where rownum between {1} and {2}", sqlStr, pageSize * (page - 1) + 1, pageSize * page, orderby);

            using (var reader = Storage.Context3.ExecuteReader(new Command(sqlStr)))
            {
                while (reader.Read())
                {
                    list.Add(Mapper.DynamicMap<SmdStoreDto>(reader));
                }
            }

            return new GridData<SmdStoreDto>(list, count);
        }

        public static GridData<DeviceDto> SearchDeviceDto(int page, int pageSize, string orderby, List<Condition> condition, string devices, string taskid = "")
        {
            // 条件
            string whereStr = " where 1=1";

            if (condition.Any())
            {
                var cd = condition.First();
                whereStr = " where DeliveryType like '%" + cd.value + "%'";
                whereStr += " or address like '%" + cd.value + "%'";
                whereStr += " or FullSetSN like '%" + cd.value + "%'";
                whereStr += " or MeiPinNo like '%" + cd.value + "%'";
                whereStr += " or AuthID like '%" + cd.value + "%'";
            }

            if (!string.IsNullOrEmpty(taskid))
            {
                var taskapi = new TaskAPI(taskid);
                var region = taskapi.GetVariableBySingleValue("Region");
                if (!string.IsNullOrEmpty(region))
                {
                    whereStr += " and s.region='" + region.Trim() + "'";
                }
                var province = taskapi.GetVariableBySingleValue("Province");
                if (!string.IsNullOrEmpty(province))
                {
                    whereStr += " and s.province='" + province.Trim() + "'";
                }
                var channel = taskapi.GetVariableBySingleValue("Channel");
                if (!string.IsNullOrEmpty(channel))
                {
                    var channel_cn = channel.Trim() == "DULUX" ? "多乐士" : "来威";
                    whereStr += " and s.channel='" + channel_cn + "'";
                }
            }

            if (devices != "")
            {
                whereStr += " AND DeviceID not in (" + devices + ")";
            }
            // 总数
            string sqlStr = "select count(1) from t_smd_device d join t_smd_Store s on d.storeid = s.storeid";
            int count = Utils.ObjToInt(Storage.Context3.ExecuteScalar(new Command(sqlStr + whereStr)), 0);

            // 结果集
            var list = new List<DeviceDto>();
            sqlStr = "select d.* from t_smd_device d join t_smd_Store s on d.storeid = s.storeid " + whereStr;

            if (string.IsNullOrEmpty(orderby))
            {
                orderby = "storeid desc";
            }
            sqlStr = string.Format(";with temp as(\r\n" +
                "	select row_number() over(order by sub.{3}) as rownum, sub.* from ({0}) sub\r\n)\r\n" +
                "select * from temp where rownum between {1} and {2}", sqlStr, pageSize * (page - 1) + 1, pageSize * page, orderby);

            using (var reader = Storage.Context3.ExecuteReader(new Command(sqlStr)))
            {
                while (reader.Read())
                {
                    list.Add(Mapper.DynamicMap<DeviceDto>(reader));
                }
            }
            return new GridData<DeviceDto>(list, count);
        }

        //序列化合同Dto数据（合同和设备一起）
        public static GridData<ContractDto> SearchContractDto(int page, int pageSize, string orderby, List<Condition> condition)
        {
            // 条件
            string whereStr = " where 1=1";

            if (condition.Any())
            {
                var cd = condition.First();
                whereStr = " where AuthID like '%" + cd.value + "%'";
                whereStr += "or StoreContractSN like '%" + cd.value + "%'";
            }
            // 总数
            string sqlStr = "select count(1) from t_smd_contract";
            int count = Utils.ObjToInt(Storage.Context3.ExecuteScalar(new Command(sqlStr + whereStr)), 0);

            // 结果集
            var list = new List<ContractDto>();
            sqlStr = "select c.*"
               + ",[ContractDeviceID], [DeviceNature], [DeviceAffiliation], [DeviceStamp], [DeviceParty], [DeviceCopies]"
               + ",[DeviceExpressDate],[DeviceEms],[ToDeviceEms],[DeviceFinance],[DeviceArchive] ,[DeviceRemark],[DeviceFixAssetNo]"
               + ",[DeviceConractSN],[DeviceDeposit],[DeviceConractType],[DeviceContractBeginDate],[DeviceContractEndDate],[DeliveryNo]"
               + ",[DeliveryDate]"
                + "  from t_smd_contract  c left join t_smd_contractDevice d on c.contractID = d.contractId" + whereStr;

            if (string.IsNullOrEmpty(orderby))
            {
                orderby = "storeid desc";
            }
            sqlStr = string.Format(";with temp as(\r\n" +
                "	select row_number() over(order by sub.{3}) as rownum, sub.* from ({0}) sub\r\n)\r\n" +
                "select * from temp where rownum between {1} and {2}", sqlStr, pageSize * (page - 1) + 1, pageSize * page, orderby);

            using (var reader = Storage.Context3.ExecuteReader(new Command(sqlStr)))
            {
                while (reader.Read())
                {
                    list.Add(Mapper.DynamicMap<ContractDto>(reader));
                }
            }
            return new GridData<ContractDto>(list, count);
        }

        //序列化合同Dto数据（合同和设备一对多）
        public static GridData<ContractDto> SearchContractDto1(int page, int pageSize, string orderby, List<Condition> condition)
        {
            // 条件
            string whereStr = " where 1=1";

            if (condition.Any())
            {
                var cd = condition.First();
                whereStr = " where AuthID like '%" + cd.value + "%'";
                whereStr += "or StoreContractSN like '%" + cd.value + "%'";
            }
            // 总数
            string sqlStr = "select count(1) from t_smd_contract";
            int count = Utils.ObjToInt(Storage.Context3.ExecuteScalar(new Command(sqlStr + whereStr)), 0);

            // 结果集
            var list = new List<ContractDto>();
            sqlStr = "select c.*"
               + ",[ContractDeviceID], [DeviceNature], [DeviceAffiliation], [DeviceStamp], [DeviceParty], [DeviceCopies]"
               + ",[DeviceExpressDate],[DeviceEms],[ToDeviceEms],[DeviceFinance],[DeviceArchive] ,[DeviceRemark],[DeviceFixAssetNo]"
               + ",[DeviceConractSN],[DeviceDeposit],[DeviceConractType],[DeviceContractBeginDate],[DeviceContractEndDate],[DeliveryNo]"
               + ",[DeliveryDate]"
                + "  from t_smd_contract  c left join t_smd_contractDevice d on c.contractID = d.contractId" + whereStr;

            if (string.IsNullOrEmpty(orderby))
            {
                orderby = "storeid desc";
            }
            sqlStr = string.Format(";with temp as(\r\n" +
                "	select row_number() over(order by sub.{3}) as rownum, sub.* from ({0}) sub\r\n)\r\n" +
                "select * from temp where rownum between {1} and {2}", sqlStr, pageSize * (page - 1) + 1, pageSize * page, orderby);

            using (var reader = Storage.Context3.ExecuteReader(new Command(sqlStr)))
            {
                while (reader.Read())
                {
                    list.Add(Mapper.DynamicMap<ContractDto>(reader));
                }
            }
            return new GridData<ContractDto>(list, count);
        }

        /// <summary>
        /// 验证在全新门店（保证金流程）未走完的门店
        /// </summary>
        /// <param name="storeid">流程未走完的门店</param>
        /// <returns></returns>
        public static List<int> FindUnFinishStore(List<int> storeids)
        {
            if (storeids == null || storeids.Count == 0)
            {
                return null;
            }

            var str_storeid = string.Join(",", storeids);

            var sqlbuilder = new StringBuilder();
            sqlbuilder.AppendLine("SELECT storeid FROM [t_StoreMasterData] WHERE BusinessKey in (");
            sqlbuilder.AppendLine("	SELECT BusinessKey FROM [t_smd_ChildProcess] AS A ");
            sqlbuilder.AppendLine(" WHERE A.Status = 'active' AND A.BusinessProcess = 'Store' AND A.Process = 'Store Deposit Receive') ");
            sqlbuilder.AppendLine("AND Process = 'Store Deposit Receive' and NewFitment = '全新门店' and storeid in (" + str_storeid + ")");

            DBUtility.DbHelperSQL.connectionString = DBUtility.PubConstant.GetConnectionString("oleDB");
            var dt_result = DBUtility.DbHelperSQL.Query(sqlbuilder.ToString()).Tables[0];

            return (from DataRow row in dt_result.Rows select Utils.ObjToInt(row["storeid"], 0)).ToList();
        }

        /// <summary>
        /// 获取主数据库的数据转成StoreMasterData
        /// </summary>
        /// <param name="storeMasterData"></param>
        /// <returns></returns>
        public static t_StoreMasterData StoreToMasterData(t_StoreMasterData storeMasterData)
        {
            if (string.IsNullOrEmpty(storeMasterData.AuthID)) return storeMasterData;
            if (storeMasterData.NewFitment == "全新门店") return storeMasterData;

            var store = GetSmdStoreByAuthID(storeMasterData.AuthID);
            if (store.StoreID == 0) return storeMasterData;

            storeMasterData.Status = store.StoreStatus;
            storeMasterData.StoreType = GetStoreType(store.StoreType);
            storeMasterData.Channel = store.Channel;
            storeMasterData.AreaName = store.Region;
            storeMasterData.ProvinceName = store.Province;
            storeMasterData.CityName = store.CityName;
            storeMasterData.MallShortName = store.MallShortName;
            storeMasterData.MallName = store.MallName;
            storeMasterData.HouseNumber = store.HouseNumber;
            storeMasterData.StoreName = store.StoreName;
            storeMasterData.ShopSeq = store.ShopSeq;
            storeMasterData.ContractPerson = store.ContactPerson;
            storeMasterData.Telephone = store.PhoneNumber;
            storeMasterData.Address = store.StoreAddress;
            storeMasterData.DisplayArea = store.StoreSize;
            storeMasterData.OpenDate = store.FirstStartDate;
            storeMasterData.IsColourService = store.IsColourService;
            storeMasterData.IsTintingService = store.IsTintingService;
            storeMasterData.ShowInWebsite = store.ShowInWebsite;
            storeMasterData.CurrentStartDate = store.CurrentStartDate;
            storeMasterData.LastRenewDate = store.LastRenewDate;

            storeMasterData.IsDowngraded = store.IsDowngraded;
            storeMasterData.BeforeDowngradedType = store.BeforeDowngradedType;

            if (!storeMasterData.NewFitment.Contains("升级"))
            {
                storeMasterData.isUpgraded = store.IsUpgraded;
                storeMasterData.BeforeUpgradedType = store.BeforeUpgradedType;
            }

            if (storeMasterData.NewFitment != "翻新")
            {
                storeMasterData.IsRenew = store.IsRenew;
                storeMasterData.BeforeRenewType = store.BeforeRenewType;
                storeMasterData.LastRenewDate = store.LastRenewDate;
            }


            return storeMasterData;
        }
        public static void SaveDealerShort(int storeid)
        {
            //SmdSync syncModel = new SmdSync();
            //List<SmdSync> syncList = new List<SmdSync>();
            DbHelperSQL.connectionString = PubConstant.GetConnectionString("smdDB");
            string sql = "update t_smd_store set DealerShortName = isnull((select top(1) Dealershortname from t_smd_SmdSyncData where t_smd_SmdSyncData.SerialNumber = t_smd_Store.EapproveSN and Authid is null),(select DealerShortName from t_smd_store where StoreID=@storeid)) where StoreID =@storeid ";
            int Execute = DbHelperSQL.ExecuteSql(sql, new SqlParameter("@storeid", storeid));
            
        
            
        }
    }
}